using CoreManager.Models.Entities;
using CoreManager.Repository.DbContext;
using Dapper;
using Dapper.Contrib.Extensions;
using Microsoft.EntityFrameworkCore;
using System.Data;
using System.Linq.Expressions;
using System.Text;

namespace CoreManager.Repository.Repositories
{
    /// <summary>
    /// Dapper仓储实现
    /// 基于Dapper的轻量级、高性能数据访问实现
    /// </summary>
    /// <typeparam name="T">实体类型，必须继承自BaseEntity</typeparam>
    public class DapperRepository<T> : IDapperRepository<T> where T : BaseEntity, new()
    {
        protected readonly CoreManagerDbContext _context;
        protected readonly IDbConnection _connection;
        protected readonly string _tableName;

        public DapperRepository(CoreManagerDbContext context)
        {
            _context = context;
            _connection = context.Database.GetDbConnection();
            _tableName = GetTableName();
        }

        public IDbConnection Connection => _connection;
        public CoreManagerDbContext Context => _context;

        #region 基础CRUD操作

        /// <summary>
        /// 根据ID获取实体
        /// </summary>
        public async Task<T?> GetByIdAsync(int id)
        {
            var sql = $"SELECT * FROM {_tableName} WHERE Id = @Id AND IsDeleted = 0";
            return await _connection.QuerySingleOrDefaultAsync<T>(sql, new { Id = id });
        }

        /// <summary>
        /// 根据条件获取单个实体
        /// </summary>
        public async Task<T?> GetAsync(Expression<Func<T, bool>> expression)
        {
            var (whereClause, parameters) = BuildWhereClause(expression);
            var sql = $"SELECT * FROM {_tableName} WHERE {whereClause} AND IsDeleted = 0";
            return await _connection.QuerySingleOrDefaultAsync<T>(sql, parameters);
        }

        /// <summary>
        /// 获取所有实体
        /// </summary>
        public async Task<List<T>> GetAllAsync()
        {
            var sql = $"SELECT * FROM {_tableName} WHERE IsDeleted = 0";
            var result = await _connection.QueryAsync<T>(sql);
            return result.ToList();
        }

        /// <summary>
        /// 根据条件获取实体列表
        /// </summary>
        public async Task<List<T>> GetListAsync(Expression<Func<T, bool>> expression)
        {
            var (whereClause, parameters) = BuildWhereClause(expression);
            var sql = $"SELECT * FROM {_tableName} WHERE {whereClause} AND IsDeleted = 0";
            var result = await _connection.QueryAsync<T>(sql, parameters);
            return result.ToList();
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        public async Task<(List<T> Items, int Total)> GetPagedListAsync(
            Expression<Func<T, bool>>? expression = null,
            int pageIndex = 1,
            int pageSize = 20,
            Expression<Func<T, object>>? orderBy = null,
            bool isAsc = true)
        {
            var whereClause = "IsDeleted = 0";
            object? parameters = null;

            // 构建查询条件
            if (expression != null)
            {
                var (customWhere, customParams) = BuildWhereClause(expression);
                whereClause += $" AND {customWhere}";
                parameters = customParams;
            }

            // 构建排序
            var orderClause = "Id";
            if (orderBy != null)
            {
                orderClause = GetPropertyName(orderBy);
            }
            orderClause += isAsc ? " ASC" : " DESC";

            // 获取总数
            var countSql = $"SELECT COUNT(*) FROM {_tableName} WHERE {whereClause}";
            var total = await _connection.QuerySingleAsync<int>(countSql, parameters);

            // 分页查询
            var offset = (pageIndex - 1) * pageSize;
            var dataSql = $"SELECT * FROM {_tableName} WHERE {whereClause} ORDER BY {orderClause} LIMIT {pageSize} OFFSET {offset}";
            var items = await _connection.QueryAsync<T>(dataSql, parameters);

            return (items.ToList(), total);
        }

        /// <summary>
        /// 添加实体
        /// </summary>
        public async Task<int> InsertAsync(T entity)
        {
            entity.CreateTime = DateTime.Now;
            entity.UpdateTime = DateTime.Now;
            
            var properties = GetInsertProperties();
            var columns = string.Join(", ", properties);
            var values = string.Join(", ", properties.Select(p => $"@{p}"));
            
            var sql = $"INSERT INTO {_tableName} ({columns}) VALUES ({values}); SELECT LAST_INSERT_ID();";
            return await _connection.QuerySingleAsync<int>(sql, entity);
        }

        /// <summary>
        /// 批量添加实体
        /// </summary>
        public async Task<int> InsertRangeAsync(List<T> entities)
        {
            var now = DateTime.Now;
            entities.ForEach(e =>
            {
                e.CreateTime = now;
                e.UpdateTime = now;
            });

            var properties = GetInsertProperties();
            var columns = string.Join(", ", properties);
            var values = string.Join(", ", properties.Select(p => $"@{p}"));
            
            var sql = $"INSERT INTO {_tableName} ({columns}) VALUES ({values})";
            return await _connection.ExecuteAsync(sql, entities);
        }

        /// <summary>
        /// 更新实体
        /// </summary>
        public async Task<bool> UpdateAsync(T entity)
        {
            entity.UpdateTime = DateTime.Now;
            
            var properties = GetUpdateProperties();
            var setClause = string.Join(", ", properties.Select(p => $"{p} = @{p}"));
            
            var sql = $"UPDATE {_tableName} SET {setClause} WHERE Id = @Id";
            var result = await _connection.ExecuteAsync(sql, entity);
            return result > 0;
        }

        /// <summary>
        /// 根据条件更新
        /// </summary>
        public async Task<bool> UpdateAsync(Expression<Func<T, bool>> expression, Expression<Func<T, T>> updateExpression)
        {
            var (whereClause, whereParams) = BuildWhereClause(expression);
            var (setClause, setParams) = BuildUpdateClause(updateExpression);
            
            var sql = $"UPDATE {_tableName} SET {setClause}, UpdateTime = @UpdateTime WHERE {whereClause}";
            
            var parameters = new DynamicParameters();
            parameters.Add("UpdateTime", DateTime.Now);
            if (whereParams != null) parameters.AddDynamicParams(whereParams);
            if (setParams != null) parameters.AddDynamicParams(setParams);
            
            var result = await _connection.ExecuteAsync(sql, parameters);
            return result > 0;
        }

        /// <summary>
        /// 删除实体
        /// </summary>
        public async Task<bool> DeleteAsync(int id)
        {
            var sql = $"DELETE FROM {_tableName} WHERE Id = @Id";
            var result = await _connection.ExecuteAsync(sql, new { Id = id });
            return result > 0;
        }

        /// <summary>
        /// 根据条件删除
        /// </summary>
        public async Task<bool> DeleteAsync(Expression<Func<T, bool>> expression)
        {
            var (whereClause, parameters) = BuildWhereClause(expression);
            var sql = $"DELETE FROM {_tableName} WHERE {whereClause}";
            var result = await _connection.ExecuteAsync(sql, parameters);
            return result > 0;
        }

        /// <summary>
        /// 软删除
        /// </summary>
        public async Task<bool> SoftDeleteAsync(int id)
        {
            var sql = $"UPDATE {_tableName} SET IsDeleted = 1, UpdateTime = @UpdateTime WHERE Id = @Id";
            var result = await _connection.ExecuteAsync(sql, new { Id = id, UpdateTime = DateTime.Now });
            return result > 0;
        }

        /// <summary>
        /// 检查是否存在
        /// </summary>
        public async Task<bool> ExistsAsync(Expression<Func<T, bool>> expression)
        {
            var (whereClause, parameters) = BuildWhereClause(expression);
            var sql = $"SELECT COUNT(*) FROM {_tableName} WHERE {whereClause} AND IsDeleted = 0";
            var count = await _connection.QuerySingleAsync<int>(sql, parameters);
            return count > 0;
        }

        /// <summary>
        /// 获取数量
        /// </summary>
        public async Task<int> CountAsync(Expression<Func<T, bool>>? expression = null)
        {
            var whereClause = "IsDeleted = 0";
            object? parameters = null;

            if (expression != null)
            {
                var (customWhere, customParams) = BuildWhereClause(expression);
                whereClause += $" AND {customWhere}";
                parameters = customParams;
            }

            var sql = $"SELECT COUNT(*) FROM {_tableName} WHERE {whereClause}";
            return await _connection.QuerySingleAsync<int>(sql, parameters);
        }

        /// <summary>
        /// 获取可查询对象（Dapper不支持IQueryable）
        /// </summary>
        public IQueryable<T> GetQueryable()
        {
            throw new NotSupportedException("Dapper仓储不支持IQueryable操作。请使用EF Core仓储或直接使用Dapper的查询方法。");
        }

        #endregion

        #region Dapper特有功能

        /// <summary>
        /// 执行查询并返回指定类型的结果
        /// </summary>
        public async Task<IEnumerable<TResult>> QueryAsync<TResult>(string sql, object? param = null, IDbTransaction? transaction = null, int? commandTimeout = null)
        {
            return await _connection.QueryAsync<TResult>(sql, param, transaction, commandTimeout);
        }

        /// <summary>
        /// 执行查询并返回单个结果
        /// </summary>
        public async Task<TResult?> QuerySingleOrDefaultAsync<TResult>(string sql, object? param = null, IDbTransaction? transaction = null, int? commandTimeout = null)
        {
            return await _connection.QuerySingleOrDefaultAsync<TResult>(sql, param, transaction, commandTimeout);
        }

        /// <summary>
        /// 执行命令
        /// </summary>
        public async Task<int> ExecuteAsync(string sql, object? param = null, IDbTransaction? transaction = null, int? commandTimeout = null)
        {
            return await _connection.ExecuteAsync(sql, param, transaction, commandTimeout);
        }

        /// <summary>
        /// 执行标量查询
        /// </summary>
        public async Task<TResult?> ExecuteScalarAsync<TResult>(string sql, object? param = null, IDbTransaction? transaction = null, int? commandTimeout = null)
        {
            return await _connection.ExecuteScalarAsync<TResult>(sql, param, transaction, commandTimeout);
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        public async Task<IEnumerable<TResult>> QueryStoredProcedureAsync<TResult>(string storedProcedure, object? param = null, IDbTransaction? transaction = null, int? commandTimeout = null)
        {
            return await _connection.QueryAsync<TResult>(storedProcedure, param, transaction, commandTimeout, CommandType.StoredProcedure);
        }

        /// <summary>
        /// 批量插入（使用Dapper.Contrib）
        /// </summary>
        public async Task<long> BulkInsertAsync(IEnumerable<T> entities, IDbTransaction? transaction = null)
        {
            var now = DateTime.Now;
            foreach (var entity in entities)
            {
                entity.CreateTime = now;
                entity.UpdateTime = now;
            }
            return await _connection.InsertAsync(entities, transaction);
        }

        /// <summary>
        /// 批量更新（使用Dapper.Contrib）
        /// </summary>
        public async Task<bool> BulkUpdateAsync(IEnumerable<T> entities, IDbTransaction? transaction = null)
        {
            foreach (var entity in entities)
            {
                entity.UpdateTime = DateTime.Now;
            }
            return await _connection.UpdateAsync(entities, transaction);
        }

        /// <summary>
        /// 开始事务
        /// </summary>
        public IDbTransaction BeginTransaction()
        {
            if (_connection.State != ConnectionState.Open)
            {
                _connection.Open();
            }
            return _connection.BeginTransaction();
        }

        /// <summary>
        /// 多结果集查询
        /// </summary>
        public async Task<T1> QueryMultipleAsync<T1>(string sql, Func<SqlMapper.GridReader, T1> map, object? param = null, IDbTransaction? transaction = null, int? commandTimeout = null)
        {
            using var multi = await _connection.QueryMultipleAsync(sql, param, transaction, commandTimeout);
            return map(multi);
        }

        #endregion

        #region 私有辅助方法

        /// <summary>
        /// 获取表名
        /// </summary>
        private string GetTableName()
        {
            var entityType = typeof(T);
            var tableAttribute = entityType.GetCustomAttributes(typeof(TableAttribute), false).FirstOrDefault() as TableAttribute;
            return tableAttribute?.Name ?? entityType.Name + "s";
        }

        /// <summary>
        /// 获取插入属性列表
        /// </summary>
        private List<string> GetInsertProperties()
        {
            var properties = typeof(T).GetProperties()
                .Where(p => p.Name != "Id" && p.CanWrite)
                .Select(p => p.Name)
                .ToList();
            return properties;
        }

        /// <summary>
        /// 获取更新属性列表
        /// </summary>
        private List<string> GetUpdateProperties()
        {
            var properties = typeof(T).GetProperties()
                .Where(p => p.Name != "Id" && p.Name != "CreateTime" && p.CanWrite)
                .Select(p => p.Name)
                .ToList();
            return properties;
        }

        /// <summary>
        /// 构建WHERE子句（简化版本，实际项目中可能需要更复杂的表达式解析）
        /// </summary>
        private (string whereClause, object? parameters) BuildWhereClause(Expression<Func<T, bool>> expression)
        {
            // 这里是一个简化的实现，实际项目中可能需要使用更复杂的表达式树解析
            // 可以考虑使用第三方库如 SqlKata 或自己实现完整的表达式解析器
            return ("1=1", null);
        }

        /// <summary>
        /// 构建UPDATE SET子句
        /// </summary>
        private (string setClause, object? parameters) BuildUpdateClause(Expression<Func<T, T>> updateExpression)
        {
            // 简化实现，实际项目中需要解析更新表达式
            return ("1=1", null);
        }

        /// <summary>
        /// 获取属性名称
        /// </summary>
        private string GetPropertyName(Expression<Func<T, object>> expression)
        {
            if (expression.Body is MemberExpression memberExpression)
            {
                return memberExpression.Member.Name;
            }
            if (expression.Body is UnaryExpression unaryExpression && unaryExpression.Operand is MemberExpression memberExpr)
            {
                return memberExpr.Member.Name;
            }
            return "Id";
        }

        #endregion
    }
}